import xlrd
import smtplib
from email.mime.text import MIMEText


def send_email(invitation, to_email):
    # 设置Email发送相关的一些参数
    from_smtp = 'smtp.163.com' # 换成用于发送消息的Email的SMTP地址，可以在Email官方的帮助文档中查询到。
    from_email = 'msgsend@163.com' # 换成自己申请的用于发送消息的Email地址
    from_email_password = '123456' # 换成自己申请的用于发送消息的Email密码

    # 将消息编码成Email的标准格式
    msg = MIMEText(invitation, _charset='utf-8')
    # 设定Email的标题
    msg['Subject'] = '百年校庆，诚挚邀请'
    msg['From'] = from_email
    msg['To'] = to_email

    # 发送Email
    s = smtplib.SMTP(from_smtp)
    s.login(from_email, from_email_password)
    s.sendmail(from_email, to_email, msg.as_string())
    s.quit()

with open('邀请函模板.txt', 'r') as fr:
    invitation_template = fr.read()

book = xlrd.open_workbook('test.xlsx')
sh = book.sheet_by_index(0)
for rx in range(sh.nrows):
    name = sh.cell_value(rowx=rx, colx=0)
    email = sh.cell_value(rowx=rx, colx=1)
    print('name: {}; Email: {}'.format(name, email))
    invitation = invitation_template.replace('[[[name]]]', name)
    send_email(invitation, email)
